﻿ using System.Data;
 using System.Configuration;
 using System.Web;
 using System.IO;
 using System.Text;
 using NPOI;
 using NPOI.HPSF;
 using NPOI.HSSF;
 using NPOI.HSSF.UserModel;
 using NPOI.HSSF.Util;
 using NPOI.POIFS;
 using NPOI.Util;

using NPOI.SS.UserModel;
using System;

     public class NPOIExcelHelper
     {
         //Datatable导出Excel
         public static HSSFWorkbook GridToExcelByNPOI(DataSet ds)
         {
             HSSFWorkbook workbook = new HSSFWorkbook();
             try
             {
                 for (int i = 0; i < ds.Tables.Count; i++)
                 {
                     DataTable dt = ds.Tables[i];
                     string sheetName = "Sheet";
                     if (string.IsNullOrEmpty(dt.TableName) == true)
                     {
                         sheetName += (i + 1).ToString();
                     }
                     else 
                     {
                         sheetName = dt.TableName;
                     }

                     ISheet sheet = workbook.CreateSheet(sheetName);
                     ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                     HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                     HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                     HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                     HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                     HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                     //字体
                     NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                     headerfont.Boldweight = (short)FontBoldWeight.Bold;
                     HeadercellStyle.SetFont(headerfont);


                     //用column name 作为列名
                     int icolIndex = 0;
                     IRow headerRow = sheet.CreateRow(0);
                     foreach (DataColumn item in dt.Columns)
                     {
                         ICell cell = headerRow.CreateCell(icolIndex);
                         cell.SetCellValue(item.ColumnName);
                         cell.CellStyle = HeadercellStyle;
                         icolIndex++;
                     }

                     ICellStyle cellStyle = workbook.CreateCellStyle();

                     //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                     cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                     cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                     cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                     cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                     cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                     NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                     cellfont.Boldweight = (short)FontBoldWeight.Normal;
                     cellStyle.SetFont(cellfont);

                     //建立内容行
                     int iRowIndex = 1;
                     int iCellIndex = 0;
                     foreach (DataRow Rowitem in dt.Rows)
                     {
                         IRow DataRow = sheet.CreateRow(iRowIndex);
                         foreach (DataColumn Colitem in dt.Columns)
                         {

                             ICell cell = DataRow.CreateCell(iCellIndex);
                             cell.SetCellValue(Rowitem[Colitem].ToString());
                             cell.CellStyle = cellStyle;
                             iCellIndex++;
                         }
                         iCellIndex = 0;
                         iRowIndex++;
                     }

                     //自适应列宽度
                     for (int j = 0; j < icolIndex; j++)
                     {
                         sheet.AutoSizeColumn(j);
                     }
                 }
             }
             catch (Exception ex)
             {
                 
             }
             finally { }

             return workbook;
         }

         /// <summary>
         /// Excel文件导成Datatable
         /// </summary>
         /// <param name="strFilePath">Excel文件目录地址</param>
         /// <param name="strTableName">Datatable表名</param>
         /// <param name="iSheetIndex">Excel sheet index</param>
         /// <returns></returns>
         public static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex)
         {

             string strExtName = Path.GetExtension(strFilePath);

             DataTable dt = new DataTable();
             if (!string.IsNullOrEmpty(strTableName))
             {
                 dt.TableName = strTableName;
             }

             if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
             {
                 using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
                 {
                     HSSFWorkbook workbook = new HSSFWorkbook(file);
                     ISheet sheet = workbook.GetSheetAt(iSheetIndex);

                     //列头
                     foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
                     {
                         dt.Columns.Add(item.ToString(), typeof(string));
                     }

                     //写入内容
                     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                     while (rows.MoveNext())
                     {
                         IRow row = (HSSFRow)rows.Current;
                         if (row.RowNum == sheet.FirstRowNum)
                         {
                             continue;
                         }

                         DataRow dr = dt.NewRow();
                         foreach (ICell item in row.Cells)
                         {
                             switch (item.CellType)
                             {
                                 case CellType.Boolean:
                                     dr[item.ColumnIndex] = item.BooleanCellValue;
                                     break;
                                 case CellType.Error:
                                     //dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                     break;
                                 case CellType.Formula:
                                     switch (item.CachedFormulaResultType)
                                     {
                                         case CellType.Boolean:
                                             dr[item.ColumnIndex] = item.BooleanCellValue;
                                             break;
                                         case CellType.Error:
                                             //dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
                                             break;
                                         case CellType.Numeric:
                                             if (DateUtil.IsCellDateFormatted(item))
                                             {
                                                 dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                             }
                                             else
                                             {
                                                 dr[item.ColumnIndex] = item.NumericCellValue;
                                             }
                                             break;
                                         case CellType.String:
                                             string str = item.StringCellValue;
                                             if (!string.IsNullOrEmpty(str))
                                             {
                                                 dr[item.ColumnIndex] = str.ToString();
                                             }
                                             else
                                             {
                                                 dr[item.ColumnIndex] = null;
                                             }
                                             break;
                                         case CellType.Unknown:
                                         case CellType.Blank:
                                         default:
                                             dr[item.ColumnIndex] = string.Empty;
                                             break;
                                     }
                                     break;
                                 case CellType.Numeric:
                                     if (DateUtil.IsCellDateFormatted(item))
                                     {
                                         dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
                                     }
                                     else
                                     {
                                         dr[item.ColumnIndex] = item.NumericCellValue;
                                     }
                                     break;
                                 case CellType.String:
                                     string strValue = item.StringCellValue;
                                     if (string.IsNullOrEmpty(strValue))
                                     {
                                         dr[item.ColumnIndex] = strValue.ToString();
                                     }
                                     else
                                     {
                                         dr[item.ColumnIndex] = null;
                                     }
                                     break;
                                 case CellType.Unknown:
                                 case CellType.Blank:
                                 default:
                                     dr[item.ColumnIndex] = string.Empty;
                                     break;
                             }
                         }
                         dt.Rows.Add(dr);
                     }
                 }
             }

             return dt;
         }
     }
